﻿namespace LMT.Db
{
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System;

    /// <summary>
    /// Base class for all Ado Adapter.
    /// </summary>
    /// <typeparam name="T">IEntity constrait</typeparam>
    public abstract class DbAdapter<T> : IDbAdapter<T> where T : IDbEntity, new()
    {
        /// <summary>
        /// 
        /// </summary>
        protected DbProvider dataProvider;

        /// <summary>
        /// Gets or sets the name of the sequence.
        /// </summary>
        /// <value>
        /// The name of the sequence.
        /// </value>
        protected virtual string SequenceName { get; set; }

        /// <summary>
        /// Gets or sets the entity provider.
        /// </summary>
        /// <value>
        /// The entity provider.
        /// </value>
        protected static EntityProvider EntityParser { get; set; }

        /// <summary>
        /// Selects the filter.
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="property">The property.</param>
        /// <returns></returns>
        public virtual U? Count<U>(string property) where U : struct
        {
            return this.ExecuteKeyWord<U>(property, "COUNT", null);
        }

        /// <summary>
        /// Counts the specified property.
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="property">The property.</param>
        /// <param name="whereCon">The where con.</param>
        /// <returns></returns>
        public virtual U? Count<U>(string property, List<Condition> whereCon) where U : struct
        {
            return this.ExecuteKeyWord<U>(property, "COUNT", whereCon);
        }

        /// <summary>
        /// Counts the specified property.
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="property">The property.</param>
        /// <param name="whereCon">The where con.</param>
        /// <returns></returns>
        public virtual U? Max<U>(string property) where U : struct
        {
            return this.ExecuteKeyWord<U>(property, "MAX", null);
        }

        /// <summary>
        /// Counts the specified property.
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="property">The property.</param>
        /// <param name="whereCon">The where con.</param>
        /// <returns></returns>
        public virtual U? Max<U>(string property, List<Condition> whereCon) where U : struct
        {
            return this.ExecuteKeyWord<U>(property, "MAX", whereCon);
        }

        /// <summary>
        /// Gets data by primary keys.
        /// </summary>
        /// <param name="entity">instance of T entity with key values</param>
        /// <example>
        /// <code>
        /// // create an instance of adoSession. 
        /// // You can create any Ado instance such as : adoBatchInfomation, adoTransaction, adoIdMaster ...
        /// // View Hafis.Library.Ado Namespace for more Ados.
        /// adoSession target = new adoSession();
        /// <para>
        /// // assign value for eSession entity. 
        /// eSession para = new eSession();
        /// para.SessionId = 2352;  // important because SessionId is the primary key in T_SESSION table. 
        /// // With other entities, primary keys is different. 
        /// // View Hafis.Library.Entities for more information about primary key in each entity type.
        /// </para>
        /// <para>
        /// // get eSession with primary key.
        /// para = target.GetByKeys(para);
        /// </para>
        /// </code>
        /// </example>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        /// <returns>instance of "T"</returns>
        public virtual T Key(T entity)
        {
            string selectSql = string.Empty;
            DbParameter[] paras = EntityParser.GetSelectKeyParameters(entity, out selectSql);
            selectSql += this.FilterString();

            return this.dataProvider.ExecuteReaderSingle<T>(selectSql, paras);
        }

        /// <summary>
        /// Insert the parameter entity into database.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <param name="autoMapId">if set to <c>true</c> : auto get lasted ID from DB to entity.</param>
        /// <example>
        /// <code>
        /// // create an instance of adoSession. 
        /// // You can create any Ado instance such as : adoBatchInfomation, adoTransaction, adoIdMaster ...
        /// // View Hafis.Library.Ado Namespace for more Ados.
        /// adoSession target = new adoSession();
        /// <para>
        /// // assign value for eSession entity. 
        /// eSession para = new eSession();
        /// para.Status = "00";
        /// para.WorkstationId = Environment.MachineName;
        /// para.UserId = "Necsv";
        /// </para>
        /// <para>
        /// // insert entity to database with get last SessionID from database becasue SessionID is autogen by Oracle.
        /// int actual = target.Insert(ref para, true);
        /// </para>
        /// <para>
        /// // insert entity to database but doesn't get last SessionID. 
        /// // It will run faster Insert(T, true) because it doesn't execute a select query after inserting.
        /// int actual = target.Insert(ref para, false);
        /// </para>
        /// </code>
        /// </example>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        /// <returns>number of affected records</returns>
        public virtual int Insert(ref T entity, bool autoMapId)
        {
            int result = 0;
            DbCommand command = null;

            try
            {
                command = this.dataProvider.BeginBatchExecute(CommandType.Text, false);

                if (autoMapId)
                {
                    command.CommandText = string.Format(EntityParser.SqlSequenceValue, this.SequenceName);
                    decimal? nextVal = dataProvider.ExecuteScalar<decimal>(command, null);
                    EntityParser.SetDbSequenceValue(entity, nextVal);
                }

                string insertSql = string.Empty;
                DbParameter[] paras = EntityParser.GetInsertParameters(entity, out insertSql);
                command.CommandText = insertSql;
                result = this.dataProvider.ExecuteNonQuery(command, paras);
            }
            finally
            {
                this.dataProvider.CloseBatchExecute(command, false);
            }

            return result;
        }

        /// <summary>
        /// Inserts the specified entities.
        /// </summary>
        /// <param name="entities">The entities.</param>
        /// <param name="autoId">if set to <c>true</c> [auto id].</param>
        /// <returns>number of affected records</returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        public virtual int Insert(List<T> entities, bool autoId)
        {
            if (entities == null)
            {
                throw new ArgumentNullException("entities");
            }

            int result = 0;

            // if the list has only one entity
            if (entities.Count == 1)
            {
                T entity = entities[0];
                result = this.Insert(ref entity, autoId);
            }
            else if (entities.Count > 1)
            {
                string insertSql = string.Empty;
                DbCommand command = null;
                decimal? nextVal = 0;
                bool commit = false;
                try 
                {
                    command = this.dataProvider.BeginBatchExecute(CommandType.Text, true);

                    foreach (T entity in entities)
                    {
                        if (autoId)
                        {
                            command.CommandText = string.Format(EntityParser.SqlSequenceValue, this.SequenceName);
                            nextVal = this.dataProvider.ExecuteScalar<decimal>(command, null);
                            EntityParser.SetDbSequenceValue(entity, nextVal);
                        }

                        DbParameter[] paras = EntityParser.GetInsertParameters(entity, out insertSql);
                        command.CommandText = insertSql;
                        result += this.dataProvider.ExecuteNonQuery(command, paras);
                    }

                    commit = result == entities.Count;
                }
                finally
                {
                    this.dataProvider.CloseBatchExecute(command, commit);
                }
            }

            return result;
        }

        /// <summary>
        /// Gets all data of T.
        /// </summary>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        /// <returns>List instance of "T"</returns>
        public virtual List<T> Select()
        {
            T entity = Activator.CreateInstance<T>();
            return this.Select(entity);
        }

        /// <summary>
        /// Selects the specified max return.
        /// </summary>
        /// <param name="maxReturn">The max return.</param>
        /// <returns></returns>
        public virtual List<T> Select(int maxReturn)
        {
            T entity = Activator.CreateInstance<T>();
            string selectSql = string.Format(EntityParser.SqlSelectAll, "*" ,entity.TableMapping);
            if (maxReturn != int.MaxValue)
            {
                selectSql = this.ProcessLimitReturn(maxReturn, selectSql);
            }

            return this.dataProvider.ExecuteReaderList<T>(selectSql);
        }

        /// <summary>
        /// Gets all data of T.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        /// <returns>List instance of "T"</returns>
        public virtual List<T> Select(T entity)
        {
            string selectSql = string.Format(EntityParser.SqlSelectAll, "*", entity.TableMapping);
            return this.dataProvider.ExecuteReaderList<T>(selectSql);
        }

        /// <summary>
        /// Selects the filter.
        /// </summary>
        /// <param name="whereCon">The where con.</param>
        /// <returns></returns>
        public virtual List<T> Select(List<Condition> whereCon)
        {
            T t = Activator.CreateInstance<T>();
            return this.InternalSelect(int.MaxValue, t, whereCon);
        }

        /// <summary>
        /// Selects the specified maximum.
        /// </summary>
        /// <param name="maxReturn">The maximum.</param>
        /// <param name="whereCon">The where con.</param>
        /// <returns></returns>
        public virtual List<T> Select(int maxReturn, List<Condition> whereCon)
        {
            T t = Activator.CreateInstance<T>();
            return this.InternalSelect(maxReturn, t, whereCon);
        }

        /// <summary>
        /// Selects the filter.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <param name="whereCon">The where con.</param>
        /// <returns></returns>
        protected virtual List<T> Select(T entity, List<Condition> whereCon)
        {
            return this.InternalSelect(int.MaxValue, entity, whereCon);
        }

        /// <summary>
        /// Selects the specified query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="paras">The paras.</param>
        /// <returns></returns>
        protected virtual List<T> Select(string query, DbParameter[] paras)
        {
            return this.dataProvider.ExecuteReaderList<T>(query, paras);
        }

        /// <summary>
        /// Selects the specified query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="paras">The paras.</param>
        /// <returns></returns>
        protected virtual T SelectSingle(string query, DbParameter[] paras)
        {
            return this.dataProvider.ExecuteReaderSingle<T>(query, paras);
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="dataCom">The data COM.</param>
        /// <param name="paras">The paras.</param>
        /// <returns></returns>
        protected U? ExecuteScalar<U>(string query, DbParameter[] paras) where U : struct
        {
            return this.dataProvider.ExecuteScalar<U>(query, paras);
        }

        /// <summary>
        /// Executes the scalar.6
        /// </summary>
        /// <param name="dataCom">The data COM.</param>
        /// <param name="paras">The paras.</param>
        /// <returns></returns>
        protected object ExecuteScalar(DbCommand dataCom, DbParameter[] paras)
        {
            return this.dataProvider.ExecuteScalar(dataCom, paras);
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns></returns>
        protected object ExecuteScalar(string query)
        {
            return this.dataProvider.ExecuteObject(query);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="dataCom">The data COM.</param>
        /// <param name="paras">The paras.</param>
        /// <returns></returns>
        protected int ExecuteNonQuery(DbCommand dataCom, DbParameter[] paras)
        {
            return this.dataProvider.ExecuteNonQuery(dataCom, paras);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="sqlQuery">The SQL query.</param>
        /// <param name="paras">The paras.</param>
        /// <returns></returns>
        protected int ExecuteNonQuery(string sqlQuery, DbParameter[] paras)
        {
            return this.dataProvider.ExecuteNonQuery(sqlQuery, paras);
        }

        /// <summary>
        /// Updates the parameter entity into database.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <returns>
        /// number of affected records
        /// </returns>
        /// <example>
        ///   <code>
        /// // create an instance of adoSession.
        /// // You can create any Ado instance such as : adoBatchInfomation, adoTransaction, adoIdMaster ...
        /// // View Hafis.Library.Ado Namespace for more Ados.
        /// adoSession target = new adoSession();
        ///   <para>
        /// // Get old eSession data, or create new eSession from user input data.
        /// eSession para = new eSession();
        /// para.SessionId = 2352; // important because SessionId is the primary key in T_SESSION table.
        /// para.Status = "01";
        /// para.WorkstationId = Environment.MachineName;
        /// para.UserId = "Necsv";
        ///   </para>
        /// // update the entity
        /// int actual = target.Update(para);
        ///   </code>
        ///   </example>
        ///   
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        public virtual int Update(T entity)
        {
            string updateSql = string.Empty;
            DbParameter[] paras = EntityParser.GetUpdateParemeters(entity, null, out updateSql);

            return this.dataProvider.ExecuteNonQuery(updateSql, paras);
        }

        /// <summary>
        /// Updates the list of entities.
        /// </summary>
        /// <param name="entities">The list entities.</param>
        /// <returns>number of affected records</returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        /// <returns>number of affected records</returns>
        public virtual int Update(List<T> entities)
        {
            if (entities == null)
            {
                throw new ArgumentNullException("entities");
            }

            int result = 0;

            // if the list has only one entity
            if (entities.Count == 1)
            {
                return this.Update(entities[0]);
            }
            else if (entities.Count > 1)
            {
                string updateSql = string.Empty;
                DbCommand command = null;
                bool commit = false;

                try
                {
                    command = this.dataProvider.BeginBatchExecute(CommandType.Text, true);

                    foreach (T entity in entities)
                    {
                        DbParameter[] paras = EntityParser.GetUpdateParemeters(entity, null, out updateSql);
                        command.CommandText = updateSql;
                        result += this.dataProvider.ExecuteNonQuery(command, paras);
                    }

                    commit = result == entities.Count;
                }
                finally
                {
                    this.dataProvider.CloseBatchExecute(command, commit);
                }
            }

            return result;
        }

        /// <summary>
        /// Updates the specified entity.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <param name="whereCon">The where con.</param>
        /// <returns></returns>
        public virtual int Update(T entity, List<Condition> whereCon)
        {
            string updateSql = string.Empty;
            DbParameter[] paras = EntityParser.GetUpdateParemeters(entity, whereCon, out updateSql);

            return this.dataProvider.ExecuteNonQuery(updateSql, paras);
        }

        /// <summary>
        /// Deletes the specified entity.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        /// <returns>number of affected records</returns>
        public virtual int Delete(T entity)
        {
            string deleteSql = string.Empty;
            DbParameter[] paras = EntityParser.GetDeleteParameters(entity, out deleteSql);

            return this.dataProvider.ExecuteNonQuery(deleteSql, paras);
        }

        /// <summary>
        /// Deletes the specified entity.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <param name="whereCon">The where con.</param>
        /// <returns></returns>
        public virtual int Delete(List<Condition> whereCon)
        {
            T entity = Activator.CreateInstance<T>();
            string deleteSql = string.Empty;

            DbParameter[] paras = EntityParser.GetWhereParameters(entity, whereCon, string.Empty, out deleteSql);
            deleteSql = string.Format(EntityParser.SqlDelete, entity.TableMapping, deleteSql);

            return this.dataProvider.ExecuteNonQuery(deleteSql, paras);
        }

        /// <summary>
        /// Gets the sequence.
        /// </summary>
        /// <param name="sequenceName">Name of the sequence.</param>
        /// <returns></returns>
        public U? SequenceValue<U>() where U : struct
        {
            if (string.IsNullOrEmpty(this.SequenceName))
            {
                throw new ArgumentNullException("Sequence Name is empty.");
            }

            string squenceValue = string.Format(EntityParser.SqlSequenceValue, this.SequenceName);
            return this.dataProvider.ExecuteScalar<U>(squenceValue);
        }

        /// <summary>
        /// Begins the batch execute.
        /// </summary>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="useTransaction">if set to <c>true</c> [use transaction].</param>
        /// <returns></returns>
        protected DbCommand BeginBatchExecute(CommandType commandType, bool useTransaction)
        {
            return this.dataProvider.BeginBatchExecute(commandType, useTransaction);
        }

        /// <summary>
        /// Closes the batch execute.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="commitTransaction">if set to <c>true</c> [commit transaction].</param>
        protected void CloseBatchExecute(DbCommand command, bool commitTransaction)
        {
            this.dataProvider.CloseBatchExecute(command, commitTransaction);
        }

        /// <summary>
        /// Fills the table.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="table">The table.</param>
        public void FillTable(string query, DataTable table)
        {
            this.dataProvider.FillTable(query, table);
        }

        /// <summary>
        /// Return the filter the string when select data.
        /// </summary>
        /// <returns>filter string for getting data</returns>
        protected virtual string FilterString()
        {
            return string.Empty;
        }

        /// <summary>
        /// Parses the limit.
        /// </summary>
        /// <param name="maximum">The maximum.</param>
        /// <param name="generatedSql">The generated SQL.</param>
        /// <returns></returns>
        protected abstract string ProcessLimitReturn(int maximum, string generatedSql);

        /// <summary>
        /// Internals the select.
        /// </summary>
        /// <param name="maximum">The maximum.</param>
        /// <param name="entity">The entity.</param>
        /// <param name="whereCon">The where con.</param>
        /// <returns></returns>
        private List<T> InternalSelect(int maximum, T entity, List<Condition> whereCon)
        {
            string text = string.Empty;

            DbParameter[] queryPara = EntityParser.GetSelectParemeters(entity, whereCon, string.Empty, out text);
            text += this.FilterString();

            if (maximum != int.MaxValue)
            {
                text = this.ProcessLimitReturn(maximum, text);
            }

            return this.dataProvider.ExecuteReaderList<T>(text, queryPara);
        }

        /// <summary>
        /// Selects the filter.
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="property">The property.</param>
        /// <param name="sqlKey">The SQL key.</param>
        /// <param name="whereCon">The where con.</param>
        /// <returns></returns>
        private U? ExecuteKeyWord<U>(string property, string sqlKey, List<Condition> whereCon) where U : struct
        {
            T t = Activator.CreateInstance<T>();

            string dbField = EntityParser.GetDbFieldOnProperty(t, property);
            if (string.IsNullOrEmpty(dbField))
            {
                throw new KeyNotFoundException("Property is not mapped with any column");
            }

            string selectSql = string.Empty;

            if (whereCon != null)
            {
                DbParameter[] paras = EntityParser.GetWhereParameters(t, whereCon, string.Empty, out selectSql);
                selectSql = string.Format(EntityParser.SqlSelectCondition, string.Format("{0}({1})", sqlKey, dbField), t.TableMapping, selectSql);
                selectSql += this.FilterString();
                return this.dataProvider.ExecuteScalar<U>(selectSql, paras);
            }
            else
            {
                selectSql = string.Format(EntityParser.SqlSelectAll, string.Format("{0}({1})", sqlKey, dbField), t.TableMapping);
                selectSql += this.FilterString();
                return this.dataProvider.ExecuteScalar<U>(selectSql);
            }
        }
    }
}